import numpy as np
import pandas as pd
from pypinyin import pinyin, Style
import re, datetime, arrow, os



"""将中文转换为拼音首字母

Keyword arguments:
name -- 中文
Return: initials.upper() -- 中文大写
"""

def chinese_to_pinyin_initials(name):
    # 获取每个字的拼音首字母
    initials = ''.join([word[0][0]
                       for word in pinyin(name, style=Style.NORMAL)])
    return initials.upper()  # 转换为大写

"""读取excel文件并处理患者基本数据信息

Keyword arguments:
input_path -- 文件所在目录
file_list -- 文件列表名称
Return: result -- 处理后的数据

"""

def process_patient_data(input_path, file_list):

    # 初始化表格数据 - 使用空字典初始化，避免空DataFrame连接警告
    result1 = None
    result2 = None
    result3 = None
    result4 = None

    for file in file_list:
        path = os.path.join(input_path, file)
        excel_df = pd.read_excel(path,0)

        # 性别列处理
        if '性别' in excel_df.columns:
            gender = excel_df['性别'].apply(lambda x: 2 if x == '女' else 1)
        elif 'sex' in excel_df.columns:
            gender = excel_df['sex'].apply(lambda x: 2 if x == 'Female' else 1)
        else:
            gender = 0


        # 用户微信openId
        chat_open_id = "new_patient_default_openid"

        # 所属组织机构ID
        organize_id = 2

        # 组别编码
        if '诊断1' in excel_df.columns:
            excel_df['诊断8'] = excel_df['诊断8'].astype(str)
            excel_df['诊断9'] = excel_df['诊断9'].astype(str)
            group_code = (
                    excel_df['诊断1'].str.contains('糖尿病', na=False) |
                    excel_df['诊断2'].str.contains('糖尿病', na=False) |
                    excel_df['诊断3'].str.contains('糖尿病', na=False) |
                    excel_df['诊断4'].str.contains('糖尿病', na=False) |
                    excel_df['诊断5'].str.contains('糖尿病', na=False) |
                    excel_df['诊断6'].str.contains('糖尿病', na=False) |
                    excel_df['诊断7'].str.contains('糖尿病', na=False) |
                    excel_df['诊断8'].str.contains('糖尿病', na=False) |
                    excel_df['诊断9'].str.contains('糖尿病', na=False)
            ).map({True: '7', False: '1'})
        elif '组别' in excel_df.columns:
            group_code = excel_df['组别'].replace({'MDD': '1', 'AD': '2'})
        elif '研究名称' in excel_df.columns:
            print(excel_df['研究名称'])
            # group_code = (
            #         excel_df['研究名称'].str.contains('糖尿病', na=False)
            # ).map({True: '7', False: '1'})
            group_code = excel_df.index.map(lambda x: '7' if x % 2 == 0 else '5')
            print(group_code)
        elif file == 'data20250721.xlsx':
            group_code = '6'
        else:
            group_code = '1'

        # 子队列名称
        if '子队列名称'  not in excel_df.columns:
            group_sub_name = None
        else:
            group_sub_name = excel_df['子队列名称']


        # 门诊号
        if '门诊号' in excel_df.columns:
            # 先将门诊号列转换为字符串类型
            outpatient_number = excel_df['门诊号'].apply(lambda x: str(x).replace(' ', '').replace('/', '').replace('.0', '') if pd.notna(x) else None)
            # outpatient_number = np.where(
            #     excel_df['门诊号'].str.len() > 0, excel_df['门诊号'], '')
        else:
            outpatient_number = None

        # 住院号
        if '住院号' in excel_df.columns:
            in_hospital_number = excel_df['住院号'].astype(str).str.replace(' ', '').str.replace('.0', '', regex=True)
        else:
            in_hospital_number = None


        # 用户手机号
        if '手机号' in excel_df.columns:
            mobile = excel_df['手机号'].apply(lambda x: str(x).replace(' ', '').replace('/', '') if pd.notna(x) else None)
        elif '联系方式' in excel_df.columns:
            mobile = excel_df['联系方式']
        else:
            mobile = None

        # 姓名缩写
        if 'ID' in excel_df.columns:
            abbr_user_name = excel_df['ID'].apply(lambda x: x.upper() if pd.notna(x) else None)
        elif '姓名' not in excel_df.columns:
            abbr_user_name = None
        else:
            abbr_user_name = excel_df['姓名'].apply(chinese_to_pinyin_initials)

        # 用户姓名
        if '姓名' in excel_df.columns:
            user_name = excel_df['姓名'].str.replace(' ', '')
        elif 'ID' in excel_df.columns:
            user_name = excel_df['ID'].str.replace(' ', '')
        else:
            user_name = None

        # 医生名称
        if '研究医生' in excel_df.columns:
            doctor_name = excel_df['研究医生']
        elif '医生' in excel_df.columns:
            doctor_name = excel_df['医生']
        else:
            doctor_name = None

        # 年龄列处理
        if '年龄' in excel_df.columns:
            # 将缺失值替换为空字符串（或保留为 NaN）
            age = excel_df['年龄'].apply(lambda x: str(x).replace(' ', '').replace('.0', '').replace('岁', '') if pd.notna(x) else None)

        else:
            age = None

        # 身份证
        if '身份证' in excel_df.columns:
            id_card = excel_df['身份证']
        elif '身份证号' in excel_df.columns:
            id_card = excel_df['身份证号']
        else:
            id_card = None

        # 筛选日期(预计筛选日期)、登记日期(登记完成日期))、入组日期
        if '入院时间' in excel_df.columns:
            screen_time = excel_df['入院时间'].apply(lambda x: arrow.get(str(x)).format('YYYY-MM-DD HH:mm:ss') if pd.notna(x) else None)
            entry_time = excel_df['入院时间'].apply(lambda x: arrow.get(str(x)).format('YYYY-MM-DD HH:mm:ss') if pd.notna(x) else None)
            group_time = excel_df['入院时间'].apply(lambda x: arrow.get(str(x)).format('YYYY-MM-DD HH:mm:ss') if pd.notna(x) else None)
            create_time = excel_df['入院时间'].apply(lambda x: arrow.get(str(x)).format('YYYY-MM-DD HH:mm:ss') if pd.notna(x) else None)
            update_time = excel_df['入院时间'].apply(lambda x: arrow.get(str(x)).format('YYYY-MM-DD HH:mm:ss') if pd.notna(x) else None)
        elif '入组或复诊时间' in excel_df.columns:
            screen_time = excel_df['入组或复诊时间'].apply(lambda x: arrow.get(f"{x}-01" if pd.notna(x) and len(str(x)) <= 7 else x).format('YYYY-MM-DD HH:mm:ss') if pd.notna(x) else None)
            entry_time = excel_df['入组或复诊时间'].apply(lambda x: arrow.get(f"{x}-01" if pd.notna(x) and len(str(x)) <= 7 else x).format('YYYY-MM-DD HH:mm:ss') if pd.notna(x) else None)
            group_time = excel_df['入组或复诊时间'].apply(lambda x: arrow.get(f"{x}-01" if pd.notna(x) and len(str(x)) <= 7 else x).format('YYYY-MM-DD HH:mm:ss') if pd.notna(x) else None)
            create_time = excel_df['入组或复诊时间'].apply(lambda x: arrow.get(f"{x}-01" if pd.notna(x) and len(str(x)) <= 7 else x).format('YYYY-MM-DD HH:mm:ss') if pd.notna(x) else None)
            update_time = excel_df['入组或复诊时间'].apply(lambda x: arrow.get(f"{x}-01" if pd.notna(x) and len(str(x)) <= 7 else x).format('YYYY-MM-DD HH:mm:ss') if pd.notna(x) else None)
        elif '筛选时间' in excel_df.columns:
            screen_time = excel_df['筛选时间'].apply(lambda x: arrow.get(str(x)).format('YYYY-MM-DD HH:mm:ss') if pd.notna(x) else arrow.now().format('YYYY-MM-DD HH:mm:ss'))
            entry_time = excel_df['筛选时间'].apply(lambda x: arrow.get(str(x)).format('YYYY-MM-DD HH:mm:ss') if pd.notna(x) else arrow.now().format('YYYY-MM-DD HH:mm:ss'))
            group_time = excel_df['筛选时间'].apply(lambda x: arrow.get(str(x)).format('YYYY-MM-DD HH:mm:ss') if pd.notna(x) else arrow.now().format('YYYY-MM-DD HH:mm:ss'))
            create_time = excel_df['筛选时间'].apply(lambda x: arrow.get(str(x)).format('YYYY-MM-DD HH:mm:ss') if pd.notna(x) else arrow.now().format('YYYY-MM-DD HH:mm:ss'))
            update_time = excel_df['筛选时间'].apply(lambda x: arrow.get(str(x)).format('YYYY-MM-DD HH:mm:ss') if pd.notna(x) else arrow.now().format('YYYY-MM-DD HH:mm:ss'))
        else:
            if '筛选时间' in excel_df.columns:
                screen_time = excel_df['筛选时间'].apply(lambda x: arrow.get(f"{x}-01" if pd.notna(x) and len(str(x)) <= 7 else x).format('YYYY-MM-DD HH:mm:ss') if pd.notna(x) else None)
            else:
                screen_time = "2025-7-21 00:21:06"

            if '登记时间' in excel_df.columns:
                entry_time = excel_df['登记时间'].apply(lambda x: arrow.get(f"{x}-01" if pd.notna(x) and len(str(x)) <= 7 else x).format('YYYY-MM-DD HH:mm:ss') if pd.notna(x) else None)
            else:
                entry_time = "2025-7-21 00:21:06"

            if '入组时间' in excel_df.columns:
                group_time = excel_df['入组时间'].apply(lambda x: arrow.get(f"{x}-01" if pd.notna(x) and len(str(x)) <= 7 else x).format('YYYY-MM-DD HH:mm:ss') if pd.notna(x) else None)
            else:
                group_time = "2025-7-21 00:21:06"

            if '创建时间' in excel_df.columns:
                create_time = excel_df['创建时间'].apply(lambda x: arrow.get(f"{x}-01" if pd.notna(x) and len(str(x)) <= 7 else x).format('YYYY-MM-DD HH:mm:ss') if pd.notna(x) else None)
            else:
                create_time = "2025-7-21 00:21:06"

            if '更新时间' in excel_df.columns:
                update_time = excel_df['更新时间'].apply(lambda x: arrow.get(f"{x}-01" if pd.notna(x) and len(str(x)) <= 7 else x).format('YYYY-MM-DD HH:mm:ss') if pd.notna(x) else None)
            else:
                update_time = "2025-7-21 00:21:06"

        # 出生日期
        if '出生日期' in excel_df.columns:
            if '身份证号' in excel_df.columns:
                birthday = excel_df['身份证号'].apply(lambda x: arrow.get(x[6:10] + '-' + x[10:12] + '-' + x[12:14]).format('YYYY-MM-DD') if pd.notna(x) and len(str(x)) == 18 else None)
            else:
                birthday = excel_df['出生日期'].apply(lambda x: arrow.get(f"{x}" if pd.notna(x) and len(str(x)) <= 7 else x).format('YYYY-MM-DD'))
        elif 'birth' in excel_df.columns:
            birthday = excel_df['birth'].apply(lambda x: arrow.get(f"{x}" if pd.notna(x) and len(str(x)) <= 7 else x).format('YYYY-MM-DD'))
        else:
            birthday = None

        # 知情同意书url
        if '知情同意书url' in excel_df.columns:
            informed_url = excel_df['知情同意书url']
        else:
            informed_url = None

        # 知情同意书签署日期
        if '知情同意书签署日期' in excel_df.columns:
            signing_date = excel_df['知情同意书签署日期']
        else:
            signing_date = None

        # 研究对象编码
        if '研究对象编码' in excel_df.columns:
            research_object_code = excel_df['研究对象编码']
        else:
            research_object_code = None

        # 研究对象状态
        research_object_status = 2

        # 队列名称
        if file == 'data20250721.xlsx':
            queue_name = ''
        elif '队列' not in excel_df.columns and '队列（精准/自然）' not in excel_df.columns:
            queue_name = '自然'
        else:
            if '队列' in excel_df.columns:
                queue_name = excel_df['队列'].apply(lambda x: x if x == '精准' else '自然')
            elif '队列（精准/自然）' in excel_df.columns:
                queue_name = excel_df['队列（精准/自然）'].apply(lambda x: x if  x == '精准' else '自然')
            else:
                queue_name = ''  # 默认值

        # 脱落原因
        if '脱落原因' not in excel_df.columns:
            shedding_cause = None
        else:
            shedding_cause = excel_df['脱落原因']

        # 脱落备注
        if '脱落备注' not in excel_df.columns:
            shedding_remark = None
        else:
            shedding_remark = excel_df['脱落备注']

        # 状态
        if '状态' not in excel_df.columns:
            status = 1
        else:
            status = excel_df['状态']

        # 创建人
        if '创建人' not in excel_df.columns:
            create_user = 2
        else:
            create_user = 2

        # 更新人
        if '更新人' not in excel_df.columns:
            update_user = 2
        else:
            update_user = 2


        # 签署状态
        if '签署状态' not in excel_df.columns:
            sign_status = 0
        else:
            sign_status = excel_df['签署状态']


        dt = pd.DataFrame({
            'gender': gender,
            'chat_open_id': chat_open_id,
            'organize_id': organize_id,
            'group_code': group_code,
            'group_sub_name': group_sub_name,
            'outpatient_number': outpatient_number,
            'in_hospital_number': in_hospital_number,
            'mobile': mobile,
            'abbr_user_name': abbr_user_name,
            'user_name': user_name,
            'doctor_name': doctor_name,
            'age': age,
            'id_card': id_card,
            'screen_time': screen_time,
            'entry_time': entry_time,
            'group_time': group_time,
            'birthday': birthday,
            'informed_url': informed_url,
            'signing_date': signing_date,
            'research_object_code': research_object_code,
            'research_object_status': research_object_status,
            'queue_name': queue_name,
            'shedding_cause': shedding_cause,
            'shedding_remark': shedding_remark,
            'status': status,
            'create_user': create_user,
            'create_time': create_time,
            'update_user': update_user,
            'update_time': update_time,
            'sign_status': sign_status,
        })
        if file in ["2.xlsx","3.xlsx","4.xlsx"]:
            # 去除掉医院号相同的数据
            dt = dt.drop_duplicates(subset=['in_hospital_number', 'user_name'], keep='first')
            if result1 is None:
                result1 = dt
            else:
                result1 = pd.concat([result1, dt], ignore_index=True)
        elif file in ["data20250721.xlsx"]:
            if result3 is None:
                result3 = dt
            else:
                result3 = pd.concat([result3, dt], ignore_index=True)
        elif file in ["0.xlsx"]:
            if result4 is None:
                result4 = dt
            else:
                result4 = pd.concat([result4, dt], ignore_index=True)
        else:
            # 去除掉手机号相同的数据
            dt = dt.drop_duplicates(subset=['mobile'], keep='first')
            if result2 is None:
                result2 = dt

            else:
                result2 = pd.concat([result2, dt], ignore_index=True)

    # 去除掉医院号相同的数据
    result1 = result1.drop_duplicates(subset=['in_hospital_number', 'user_name'], keep='first')
    result2 = result2.drop_duplicates(subset=['mobile'], keep='first')
    # 处理result4中mobile非空和空值的情况
    result4 = result4.drop_duplicates(subset=['mobile'], keep='first')
    result4_nan = result4[result4['mobile'].isna()]
    result4 = pd.concat([result4, result4_nan], ignore_index=True)

    # 合并数据
    result = pd.concat([result1, result2, result3, result4], ignore_index=True)

    print('合并后数据', result)

    # 删除result数据中group_code为1的最后的476条数据
    result = result.drop(result[result['group_code'] == '1'].index[-476:])

    # 将result数据中group_code为1的最后146条数据的group_code改为7
    result.loc[result[result['group_code'] == '1'].index[-146:], 'group_code'] = '7'

    # 删除result数据中group_code为6的最后的763条数据
    result = result.drop(result[result['group_code'] == '6'].index[-763:])

    print('数据处理完成:', result)


    # 按照组别编码进行排序（1、2、7、6）
    result_127 = result[result['group_code'].isin(['1', '2', '5', '7', ])].sort_values(by=['group_code'])
    result_6 = result[result['group_code'].isin(['6'])]
    result = pd.concat([result_127, result_6], ignore_index=True)


    print(result)


    return result

""" 将得分转化成答案

scores - 得分列表
scale_name - 量表名称

Return:  answers - 答案列表
"""
def scores_to_answers(scores, scale_name):
    # 根据量表名称获取对应的映射关系
    score_answer_maps = [
        {
            'scale_name': "汉密尔顿焦虑量表",
            'map': {
                0: '无症状',
                1: '轻度',
                2: '中度',
                3: '重度',
                4: '极重度'

            }
        }
    ]
    # 找到匹配的量表名称和得分映射关系
    score_answer_map = next((item for item in score_answer_maps if item['scale_name'] == scale_name), None)
    print(score_answer_map)
    # 根据量表名称获取对应的映射关系
    answers = [score_answer_map['map'][score] for score in scores]
    return answers

""" 将选项的顺序转化成id
order - 选项顺序
question - 问题
Return:  id - 问题id
"""
def order_to_id(order, question):
    # 根据问题获取对应的映射关系（测试环境）
    # order_id_maps = [
    #         {
    #             "column_name": "PHQ_interest",
    #             "answers": [64852,64853,64854,64855]
    #         },
    #         {
    #             "column_name": "PHQ_depression",
    #             "answers": [64856,64857,64858,64859]
    #         },
    #         {
    #             "column_name": "PHQ_sleep",
    #             "answers": [64860,64861,64862,64863]
    #         },
    #         {
    #             'column_name': 'PHQ_fatigue',
    #             'answers': [64864, 64865, 64866, 64867]
    #         },
    #         {
    #             'column_name': 'PHQ_eating',
    #             'answers': [64868, 64869, 64870, 64871]
    #         },
    #         {
    #             'column_name': 'PHQ_inferiority',
    #             'answers': [64872, 64873, 64874, 64875]
    #         },
    #         {
    #             'column_name': 'PHQ_focus',
    #             'answers': [64876, 64877, 64878, 64879]
    #         },
    #         {
    #             'column_name': 'PHQ_psychomotor',
    #             'answers': [64880, 64881, 64882, 64883]
    #         },
    #         {
    #             'column_name': 'PHQ_suicide',
    #             'answers': [64884, 64885, 64886, 64887]
    #         },
    #         {
    #             'column_name': 'GAD_anxiety',
    #             'answers': [64453, 64454, 64455, 64456]
    #         },
    #         {
    #             'column_name': 'GAD_uncontrollable_worry',
    #             'answers': [64457, 64458, 64459, 64460]
    #         },
    #         {
    #             'column_name': 'GAD_overworry',
    #             'answers': [64461, 64462, 64463, 64464]
    #         },
    #         {
    #             'column_name': 'GAD_restless',
    #             'answers': [64465, 64466, 64467, 64468]
    #         },
    #         {
    #             'column_name': 'GAD_akathisia',
    #             'answers': [64469, 64470, 64471, 64472]
    #         },
    #         {
    #             'column_name': 'GAD_irritability',
    #             'answers': [64473, 64474, 64475, 64476]
    #         },
    #         {
    #             'column_name': 'GAD_dread',
    #             'answers': [64477, 64478, 64479, 64480]
    #         }
    #     ]

    # 根据问题获取对应的映射关系（正式环境）
    order_id_maps = [
        {
            "column_name": "PHQ_interest",
            "answers": [66847, 66848, 66849, 66850]
        },
        {
            "column_name": "PHQ_depression",
            "answers": [66851, 66852, 66853, 66854]
        },
        {
            "column_name": "PHQ_sleep",
            "answers": [66855, 66856, 66857, 66858]
        },
        {
            'column_name': 'PHQ_fatigue',
            'answers': [66859, 66860, 66861, 66862]
        },
        {
            'column_name': 'PHQ_eating',
            'answers': [66871, 66872, 66873, 66874]
        },
        {
            'column_name': 'PHQ_inferiority',
            'answers': [66863, 66864, 66865, 66866]
        },
        {
            'column_name': 'PHQ_focus',
            'answers': [66867, 66868, 66869, 66870]
        },
        {
            'column_name': 'PHQ_psychomotor',
            'answers': [66875, 66876, 66877, 66878]
        },
        {
            'column_name': 'PHQ_suicide',
            'answers': [66879, 66880, 66881, 66882]
        },
        {
            'column_name': 'GAD_anxiety',
            'answers': [66448, 66449, 66450, 66451]
        },
        {
            'column_name': 'GAD_uncontrollable_worry',
            'answers': [66452, 66453, 66454, 66455]
        },
        {
            'column_name': 'GAD_overworry',
            'answers': [66456, 66457, 66458, 66459]
        },
        {
            'column_name': 'GAD_restless',
            'answers': [66460, 66461, 66462, 66463]
        },
        {
            'column_name': 'GAD_akathisia',
            'answers': [66464, 66465, 66466, 66467]
        },
        {
            'column_name': 'GAD_irritability',
            'answers': [66468, 66469, 66470, 66471]
        },
        {
            'column_name': 'GAD_dread',
            'answers': [66472, 66473, 66474, 66475]
        }
    ]

    # 找到匹配的问题和得分映射关系
    order_id_map = next((item for item in order_id_maps if item['column_name'] == question), None)
    # 根据问题获取对应的映射关系
    answers = order_id_map['answers']
    # 根据得分获取对应的id
    answer_id = answers[order - 1]
    return answer_id


""" 计算量表的评估结果
scale_name - 量表名称
score - 量表得分
Return:  result - 量表评估结果
"""
def calculate_scale_result(scale_name, score):
    # 根据量表名称获取对应的映射关系（测试环境）
    # scale_result_maps = [
    #     {
    #         'scale_name': "PHQ-9",
    #         'result_map':  [
    #             {
    #                 'start': 0,
    #                 'end': 4,
    #                 'result': '无抑郁症状',
    #                 'result_id': 2488
    #             },
    #             {
    #                 'start': 5,
    #                 'end': 9,
    #                 'result': '轻度抑郁',
    #                 'result_id': 2489
    #             },
    #             {
    #                 'start': 10,
    #                 'end': 14,
    #                 'result': '中度抑郁',
    #                 'result_id': 2490
    #             },
    #             {
    #                 'start': 15,
    #                 'end': 27,
    #                 'result': '重度抑郁',
    #                 'result_id': 2491
    #                 },
    #         ]
    #     },
    #     {
    #         'scale_name': "GAD-7",
    #         'result_map': [
    #             {
    #                 'start': 0,
    #                 'end': 4,
    #                 'result': '无焦虑症状',
    #                 'result_id': 2471
    #                 },
    #             {
    #                 'start': 5,
    #                 'end': 9,
    #                 'result': '轻度焦虑',
    #                 'result_id': 2472
    #                 },
    #             {
    #                 'start': 10,
    #                 'end': 14,
    #                 'result': '中度焦虑',
    #                 'result_id': 2473
    #                 },
    #             {
    #                 'start': 15,
    #                 'end': 21,
    #                 'result': '重度焦虑',
    #                 'result_id': 2474
    #                 }
    #             ]
    #         }
    # ]

    # 根据量表名称获取对应的映射关系（正式环境）
    scale_result_maps = [
        {
            'scale_name': "PHQ-9",
            'result_map':  [
                {
                    'start': 0,
                    'end': 4,
                    'result': '无抑郁症状',
                    'result_id': 2622
                },
                {
                    'start': 5,
                    'end': 9,
                    'result': '轻度抑郁',
                    'result_id': 2623
                },
                {
                    'start': 10,
                    'end': 14,
                    'result': '中度抑郁',
                    'result_id': 2624
                },
                {
                    'start': 15,
                    'end': 27,
                    'result': '重度抑郁',
                    'result_id': 2625
                    },
            ]
        },
        {
            'scale_name': "GAD-7",
            'result_map': [
                {
                    'start': 0,
                    'end': 4,
                    'result': '无焦虑症状',
                    'result_id': 2605
                    },
                {
                    'start': 5,
                    'end': 9,
                    'result': '轻度焦虑',
                    'result_id': 2606
                    },
                {
                    'start': 10,
                    'end': 14,
                    'result': '中度焦虑',
                    'result_id': 2607
                    },
                {
                    'start': 15,
                    'end': 21,
                    'result': '重度焦虑',
                    'result_id': 2608
                    }
                ]
            }
    ]

    # 找到匹配的量表和结果映射关系
    scale_result_map = next((item for item in scale_result_maps if item['scale_name'] == scale_name), None)
    # 根据量表名称获取对应的映射关系
    result_map = scale_result_map['result_map']
    # 根据得分获取对应的评估结果
    result = next((item for item in result_map if score >= item['start'] & score <= item['end']), None)
    # 获取评估结果ID
    result_id = result['result_id'] if result else None
    return result_id


""" 患者量表数据处理
input_path - 文件路径
file_list - 文件列表
Return:  result - 量表评估结果
"""
def process_scale_data(input_path, file_list):
    # 初始化结果列表
    result = None

    # 遍历文件列表
    for file in file_list:
        path = os.path.join(input_path, file)
        excel_df = pd.read_excel(path,0)
        name = excel_df["ID"]  # 获取姓名

        # 获取PHQ-9的选项ID
        phq_interest_answer_id = excel_df['PHQ_interest'].apply(lambda x: order_to_id(x, 'PHQ_interest'))
        phq_depression_answer_id = excel_df['PHQ_depression'].apply(lambda x: order_to_id(x, 'PHQ_depression'))
        phq_sleep_answer_id = excel_df['PHQ_sleep'].apply(lambda x: order_to_id(x, 'PHQ_sleep'))
        phq_fatigue_answer_id = excel_df['PHQ_fatigue'].apply(lambda x: order_to_id(x, 'PHQ_fatigue'))
        phq_eating_answer_id = excel_df['PHQ_eating'].apply(lambda x: order_to_id(x, 'PHQ_eating'))
        phq_inferiority_answer_id = excel_df['PHQ_inferiority'].apply(lambda x: order_to_id(x, 'PHQ_inferiority'))
        phq_focus_answer_id = excel_df['PHQ_focus'].apply(lambda x: order_to_id(x, 'PHQ_focus'))
        phq_psychomotor_answer_id = excel_df['PHQ_psychomotor'].apply(lambda x: order_to_id(x, 'PHQ_psychomotor'))
        phq_suicide_answer_id = excel_df['PHQ_suicide'].apply(lambda x: order_to_id(x, 'PHQ_suicide'))

        # 获取GAD-7的选项ID
        gad_anxiety_answer_id = excel_df['GAD_anxiety'].apply(lambda x: order_to_id(x, 'GAD_anxiety'))
        gad_uncontrollable_worry_answer_id = excel_df['GAD_uncontrollable_worry'].apply(lambda x: order_to_id(x, 'GAD_uncontrollable_worry'))
        gad_over_worry_answer_id = excel_df['GAD_overworry'].apply(lambda x: order_to_id(x, 'GAD_overworry'))
        gad_restless_answer_id = excel_df['GAD_restless'].apply(lambda x: order_to_id(x, 'GAD_restless'))
        gad_akathisia_answer_id = excel_df['GAD_akathisia'].apply(lambda x: order_to_id(x, 'GAD_akathisia'))
        gad_irritability_answer_id = excel_df['GAD_irritability'].apply(lambda x: order_to_id(x, 'GAD_irritability'))
        gad_dread_answer_id = excel_df['GAD_dread'].apply(lambda x: order_to_id(x, 'GAD_dread'))

        # 计算PHQ-9总分
        phq_total_score = excel_df['PHQ_interest'] + excel_df['PHQ_depression'] + excel_df['PHQ_sleep'] + excel_df['PHQ_fatigue'] + excel_df['PHQ_eating'] + excel_df['PHQ_inferiority'] + excel_df['PHQ_focus'] + excel_df['PHQ_psychomotor'] + excel_df['PHQ_suicide'] - 9
        phq_result_id = phq_total_score.apply(lambda x: calculate_scale_result('PHQ-9', x))

        # 计算GAD-7总分
        gad_total_score = excel_df['GAD_anxiety'] + excel_df['GAD_uncontrollable_worry'] + excel_df['GAD_overworry'] + excel_df['GAD_restless'] + excel_df['GAD_akathisia'] + excel_df['GAD_irritability'] + excel_df['GAD_dread'] - 7
        gad_result_id = gad_total_score.apply(lambda x: calculate_scale_result('GAD-7', x))

        dt = pd.DataFrame({
            'ID': name,
            'phq_interest_answer_id': phq_interest_answer_id,
            'phq_depression_answer_id': phq_depression_answer_id,
            'phq_sleep_answer_id': phq_sleep_answer_id,
            'phq_fatigue_answer_id': phq_fatigue_answer_id,
            'phq_eating_answer_id': phq_eating_answer_id,
            'phq_inferiority_answer_id': phq_inferiority_answer_id,
            'phq_focus_answer_id': phq_focus_answer_id,
            'phq_psychomotor_answer_id': phq_psychomotor_answer_id,
            'phq_suicide_answer_id': phq_suicide_answer_id,
            'phq_total_score': phq_total_score,
            'phq_result_id': phq_result_id,
            'gad_anxiety_answer_id': gad_anxiety_answer_id,
            'gad_uncontrollable_worry_answer_id': gad_uncontrollable_worry_answer_id,
            'gad_over_worry_answer_id': gad_over_worry_answer_id,
            'gad_restless_answer_id': gad_restless_answer_id,
            'gad_akathisia_answer_id': gad_akathisia_answer_id,
            'gad_irritability_answer_id': gad_irritability_answer_id,
            'gad_dread_answer_id': gad_dread_answer_id,
            'gad_total_score': gad_total_score,
            'gad_result_id': gad_result_id,
            })

        # 添加PHQ-9的选项ID列表列表
        dt['PHQ_answer_ids'] = dt.apply(lambda row: [
            str(row['phq_interest_answer_id']),
            str(row['phq_depression_answer_id']),
            str(row['phq_sleep_answer_id']),
            str(row['phq_fatigue_answer_id']),
            str(row['phq_eating_answer_id']),
            str(row['phq_inferiority_answer_id']),
            str(row['phq_focus_answer_id']),
            str(row['phq_psychomotor_answer_id']),
            str(row['phq_suicide_answer_id'])
        ], axis=1).apply(lambda x: '["' + ','.join(x) + '"]')

        # 添加GAD-7的选项ID列表列表
        dt['GAD_answer_ids'] = dt.apply(lambda row: [
            str(row['gad_anxiety_answer_id']),
            str(row['gad_uncontrollable_worry_answer_id']),
            str(row['gad_over_worry_answer_id']),
            str(row['gad_restless_answer_id']),
            str(row['gad_akathisia_answer_id']),
            str(row['gad_irritability_answer_id']),
            str(row['gad_dread_answer_id'])
            ], axis=1).apply(lambda x: '["' + ','.join(x) + '"]')

        if result is None:
            result = dt
        else:
            result = pd.concat([result, dt])

    return result

""" 读取MD文件
fold - 文件夹路径
"""
def read_md_file(fold):
    # 初始化表格数据 - 使用空字典初始化，避免空DataFrame连接警告
    result = None

    # 遍历文件夹中的所有.md文件
    for file in os.listdir(fold):
        if file.endswith(".md"):
            path = os.path.join(fold, file)

            # 读取md文件内容
            with open(path, 'r', encoding='utf-8') as f:
                content = f.read()
                user_name = re.findall(r'姓名(.+?)科别', content)[0]    # 获取姓名
                in_hospital_number = re.findall(r'住院号(.+?)\n', content)[0]   # 获取住院号
                scale_name = re.findall(r'医院 (.+?) +\n', content)[0]   # 获取量表名称
                assessor = re.findall(r'(.+?) 评估员', content)[0] if '评估员' in content else None  # 获取评估人
                assess_time = re.findall(r'评估日期:(.+?)\n', content)[0] if '评估日期' in content else None  # 获取评估时间
                # scores = [int(s) for s in re.findall(r'\|\s*\d+\s*\|\s*[^|]+\s*\|\s*[^|]+\s*\|\s*(\d+)\s*\|', content)] # 获取得分并将字符串得分转换为整数
                scores = [int(s) for s in re.findall(r'\|\s*\d+?\s*\|\s*[^|]+\s*\|\s*[^|]*\s*\|\s*(\d+)\s*\|', content)]  # 获取得分并将字符串得分转换为整数
                # answers = scores_to_answers(scores, scale_name) # 将得分转换为答案
                # t_score = int(re.findall(r'\|\s*总分+\s*\|\s*\|\s*\|\s*(\d+)\s*\|\s*\n', content)[0]) if '总分' in content else None # 获取总分

                print(content)
                print("姓名",user_name)
                print("住院号",in_hospital_number)
                print("量表名称",scale_name)
                print("评估人",assessor)
                print("评估时间",assess_time)
                print('得分', scores)
                # print('答案', answers)
                # print('总分', t_score)



    return result



if __name__ == "__main__":
    # 文件路径
    file_path = "./input/"

    # 患者基本数据文件列表
    files = [
        '0.xlsx',
        '2.xlsx',
        '3.xlsx',
        '4.xlsx',
        'crf.xlsx',
        'data20250721.xlsx',
        ]
    output_path = "./output/result_" + datetime.datetime.now().strftime("%Y%m%d%H%M%S") + '.xlsx'


    # 表头对应的字段
    headers = [
        ('gender', '性别'),
        ('chat_open_id', '用户微信openId'),
        ('organize_id', '所属组织机构ID'),
        ('group_code', '组别编码'),
        ('group_sub_name', '子队列名称'),
        ('outpatient_number', '门诊号'),
        ('in_hospital_number', '住院号'),
        ('mobile', '用户手机号'),
        ('abbr_user_name', '姓名缩写'),
        ('user_name', '用户姓名'),
        ('doctor_name', '研究医生姓名'),
        ('age', '年龄'),
        ('id_card', '身份证'),
        ('screen_time', '筛选日期(预计筛选日期)'),
        ('entry_time', '登记日期(登记完成日期))'),
        ('group_time', '入组日期'),
        ('birthday', '生日'),
        ('informed_url', '知情同意书url'),
        ('signing_date', '知情同意书签署日期'),
        ('research_object_code', '研究对象编码'),
        ('research_object_status', '研究对象状态'),
        ('queue_name', '队列名称'),
        ('shedding_cause', '脱落原因 '),
        ('shedding_remark', '脱落备注'),
        ('status', '状态'),
        ('create_user', '创建人'),
        ('create_time', '创建时间/预计登记时间'),
        ('update_user', '修改人'),
        ('update_time', '修改时间'),
        ('sign_status', '签署状态'),
    ]

    # 读取excel文件并处理患者基础数据
    df = process_patient_data(file_path, files)

    # 更改表头为英文表头
    # df.columns = [header for header, _ in headers]

    # 更改表头为中文表头
    df.columns = [header_ for _, header_ in headers]

    # 导出患者基本信息
    df.to_excel(output_path, index=False,sheet_name='患者基础数据')


    # 患者量表数据
    files_scale = [
        'data20250721.xlsx',
    ]

    # 读取excel文件并处理患者基础数据
    df_scale = process_scale_data(file_path, files_scale)

    # 文件夹路径
    md_fold = './input/md/'

    # read_md_file(md_fold)

    # 创建 ExcelWriter 对象
    with pd.ExcelWriter(output_path, engine='openpyxl') as writer:
        # 将 df1 写入第一个工作表，命名为 "Sheet1"
        df.to_excel(writer, sheet_name='患者基础数据', index=False)

        # 将 df2 写入第二个工作表，命名为 "Sheet2"
        df_scale.to_excel(writer, sheet_name='患者量表信息', index=False)




